package database;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import common.*;

public class BookManager {
	
	/**
	 * get all books from db
	 * param
	 * return books list
	 */
	
	public static ArrayList<Book> getAllBooks(){
		
		ArrayList<Book> AllBooks= new ArrayList<Book>();
		Book temp = new Book();
		DBManager db = DBManager.getInstance();
		Statement stmt;
		try {
			stmt = db.conn.createStatement();
			ResultSet rs= stmt.executeQuery("SELECT * FROM project.book;");
			   while( rs.next())
			   {
				   temp.setId(rs.getInt("idBook"));
				   temp.setPath(rs.getString("pathBook"));
				   temp.setTitle(rs.getString("titelBook"));
				   AllBooks.add(temp);
				   temp=new Book();
			   }
			   rs.close();
			   return AllBooks;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("EXEPTION: getAllBooks function exeption!");
			return null;
		}//catch
		
		
	}
	
	/**
	 * get books id list that wriiten by specific author
	 * param author id
	 * return books id list
	 */

	public static ArrayList<Book> getBookListByAuthor(int id){
		
		DBManager db = DBManager.getInstance();
		ArrayList<Book> AuthorBooks= new ArrayList<Book>();
		Book temp = new Book();
		int bookIDtemp;
		Statement stmt;
		try {
			stmt = db.conn.createStatement();
			ResultSet rs= stmt.executeQuery("SELECT * FROM project.book_has_author WHERE author_idAuthor='" + id +"';");
			while(rs.next()){	
				bookIDtemp=rs.getInt("book_idBook");
				temp=getBookById(bookIDtemp);
				AuthorBooks.add(temp);
			}//while
			rs.close();
			return AuthorBooks;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("EXEPTION: getBookListByAuthor function exeption!");
			e.printStackTrace();
			return null;
		}//catch
		
	
 }
	/**
	 * get book by id
	 * @param book id
	 * @return book
	 */
	public static Book getBookById(int id){
		DBManager db = DBManager.getInstance();
		Book temp=new Book();
		Statement stmt;
		try {
			stmt = db.conn.createStatement();
			ResultSet rs= stmt.executeQuery("SELECT * FROM project.book WHERE idBook='" + id +"';");
			if(rs.next()){    
			    temp.setId(rs.getInt("idBook"));
			    temp.setPath(rs.getString("pathBook"));
			    temp.setTitle(rs.getString("titelBook"));
			    rs.close();
			    return temp;
			}
				rs.close();
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("EXEPTION: getBookListByAuthor function exeption!11");
			e.printStackTrace();
			return null;
		}//catch
 }//getBookById

	
/**
 * add author to db
 * @param a author
 * @return if author addition successfully return true else return false
 */

public static boolean addBook(Book b){

	   DBManager db = DBManager.getInstance();
	   
     	if(b.getAuthors().isEmpty())
     	{
     		System.out.println("Authors array empty");
     		return false;
     	}

	   try {
		
		String sql ="INSERT INTO project.book (`idBook`,`pathBook`,`titelBook`) " +
				"VALUES(?,?,?);";
		PreparedStatement stmt = db.conn.prepareStatement(sql);
      	stmt.setInt(1, b.getId());
      	stmt.setString(2, b.getPath());
      	stmt.setString(3, b.getTitel());
      	
      	stmt.executeUpdate();
      	
      	for(int i=0;i<b.getAuthors().size();i++){
      		sql="INSERT INTO project.book_has_author (`book_idBook`,`author_idAuthor`) " +"VALUES(?,?);";
      		PreparedStatement stmt1=db.conn.prepareStatement(sql);
      		stmt1.setInt(1, b.getId());
      		stmt1.setInt(2, b.getAuthors().get(i).getId());
          	stmt1.executeUpdate();
          	stmt1.close();
      	}
      	stmt.close();

      	return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("EXEPTION: BookManager.addBook() function exeption!");
		
		deleteBook(b.getId());
		return false;
	}
	   
}

public static boolean deleteBook(int id)
{
	DBManager db = DBManager.getInstance();
	String sql=new String();
	
	sql="DELETE FROM project.book_has_author WHERE book_idBook=?;";
	PreparedStatement stmt1;
	
	try {
		stmt1= db.conn.prepareStatement(sql);
		stmt1.setInt(1, id);
		stmt1.executeUpdate();
	
	
		sql ="DELETE FROM project.book WHERE idBook=?;";
		PreparedStatement stmt;
	
		stmt = db.conn.prepareStatement(sql);
		stmt.setInt(1, id);
		stmt.executeUpdate();
		return true;
	
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
		System.out.println("EXEPTION: BookManager.deleteBook() function exeption!");
		return false;
	}
}

}